import tkinter as tk
from tkinter import *
from tkinter import ttk
import sqlite3

def mainPage():
    win = tk.Tk()
    win.geometry('200x220')
    win.title('图书库存管理系统')
    Monty = ttk.LabelFrame(win, text='图书库存管理系统v0.2')
    Monty.grid(row=0, column=0, padx=10, pady=10)


    # 显示图书信息
    def booklist():
        child = tk.Toplevel()
        child.geometry('1200x300')
        child.title('图书库存管理系统')
        Monty = ttk.LabelFrame(child, text='书目信息')
        Monty.grid(row=0, column=0, padx=10, pady=10)

        LabelName = Label(Monty, text="书名", font=('华文新魏', '16'))
        LabelNumber = Label(Monty, text="书号", font=('华文新魏', '16'))
        LabelTime = Label(Monty, text="出版时间", font=('华文新魏', '16'))
        LabelPrice = Label(Monty, text="价格", font=('华文新魏', '16'))
        LabelInventory = Label(Monty, text="库存",font=('华文新魏', '16'))

        LabelName.grid(row=0, column=0)
        LabelNumber.grid(row=0, column=1)
        LabelTime.grid(row=0, column=2)
        LabelPrice.grid(row=0, column=3)
        LabelInventory.grid(row=0, column=4)
        txtName = StringVar()
        txtNumber = StringVar()
        txtTime = StringVar()
        txtPrice = StringVar()
        txtInventory = StringVar()
        n = ''
        m = ''
        t = ''
        p = ''
        i = ''
        entryName = Label(Monty, textvariable=txtName, width=20, font=('宋体', '16'))
        entryNumber = Label(Monty, textvariable=txtNumber, width=20, font=('宋体', '16'))
        entryTime = Label(Monty, textvariable=txtTime, width=20, font=('宋体', '16'))
        entryPrice = Label(Monty, textvariable=txtPrice, width=20, font=('宋体', '16'))
        entryInventory = Label(Monty, textvariable=txtInventory, width=20, font=('宋体', '16'))
        entryName.grid(row=1, column=0)
        entryNumber.grid(row=1, column=1)
        entryTime.grid(row=1, column=2)
        entryPrice.grid(row=1, column=3)
        entryInventory.grid(row=1, column=4)
        conn = sqlite3.connect("test.db")
        cur = conn.cursor()
        sqlstr = "select * from user"
        s = cur.execute(sqlstr)
        for row in s:
            n = row[1] + '\n' + n
            m = row[0] + '\n' + m
            t = row[2] + '\n' + t
            p = str(row[3]) + '\n' + p
            i = str(row[4]) + '\n' + i
        txtName.set(n)
        txtNumber.set(m)
        txtTime.set(t)
        txtPrice.set(p)
        txtInventory.set(i)
        conn.close()

        child.mainloop()





    # 添加图书信息
    def addbook():
        child = tk.Toplevel()
        child.geometry('450x200')
        child.title('图书库存管理系统')
        Monty = ttk.LabelFrame(child, text='添加图书信息')
        Monty.grid(row=0, column=0, padx=10, pady=10)


        def add_book_info():
            try:
                conn = sqlite3.connect("test.db")
                cur = conn.cursor()
                sql = '''insert into user (num,bookname,Ptime,price,inventory)values (:st_num,:st_bookname,:st_Ptime,:st_price,:st_inventory)'''
                cur.execute(sql, {'st_num': txtNumber.get(), 'st_bookname': txtName.get(), 'st_Ptime': txtTime.get(),
                                  'st_price': txtPrice.get(), 'st_inventory': txtAmount.get(), })
                conn.commit()
                conn.close()
            except Exception:
                txtTips.set('添加失败(请检查书号是否有重复)')
            else:
                txtTips.set('图书添加成功')


        LabelName = Label(Monty, text="书号：", font=('华文新魏', '16'))
        LabelNumber = Label(Monty, text="书名：", font=('华文新魏', '16'))
        LabelTime = Label(Monty, text="出版时间：", font=('华文新魏', '16'))
        LabelPrice = Label(Monty, text="价格：", font=('华文新魏', '16'))
        LabelAmount = Label(Monty, text="库存：", font=('华文新魏', '16'))

        txtName = StringVar()
        txtNumber = StringVar()
        txtTime = StringVar()
        txtPrice = StringVar()
        txtAmount = StringVar()
        txtTips = StringVar()
        txtTips.set('请输入相关信息')
        LabelTips = Label(Monty, textvariable=txtTips, font=('华文新魏', '16'), relief='ridge', width=30)

        entryName = Entry(Monty, textvariable=txtName, width=16, font=('宋体', '16'))
        entryNumber = Entry(Monty, textvariable=txtNumber, width=16, font=('宋体', '16'))
        entryTime = Entry(Monty, textvariable=txtTime, width=16, font=('宋体', '16'))
        entryPrice = Entry(Monty, textvariable=txtPrice, width=16, font=('宋体', '16'))
        entryAmount = Entry(Monty, textvariable=txtAmount, width=16, font=('宋体', '16'))
        buttonSub = Button(Monty, text='提交', command=add_book_info, font=('宋体', '16'))

        LabelName.grid(row=0, column=0)
        LabelNumber.grid(row=1, column=0)
        LabelTime.grid(row=2, column=0)
        LabelPrice.grid(row=3, column=0)
        LabelAmount.grid(row=4, column=0)
        LabelTips.grid(row=5, column=0, columnspan=2)
        entryName.grid(row=0, column=1)
        entryNumber.grid(row=1, column=1)
        entryTime.grid(row=2, column=1)
        entryPrice.grid(row=3, column=1)
        entryAmount.grid(row=4, column=1)

        buttonSub.grid(row=5, column=2)
        child.mainloop()

    # 修改库存信息
    def updatabook():
        child = tk.Toplevel()
        child.geometry('450x150')
        child.title('书库存管理系统')
        Monty = ttk.LabelFrame(child, text='修改库存信息')
        Monty.grid(row=0, column=0, padx=10, pady=10)
        conn = sqlite3.connect("test.db")
        cur = conn.cursor()
        def updata_books():
            try:
                sql_updata = "update user set inventory = ? where num = ?"
                cur.execute(sql_updata, (txtAmount.get(), txtNumber.get()))
                conn.commit()
                conn.close()
            except Exception:
                txtTips.set('书号不正确')
            else:
                txtTips.set('图书修改成功')


        LabelNumber = Label(Monty, text="书号：", font=('华文新魏', '16'))
        LabelAmount = Label(Monty, text="库存：", font=('华文新魏', '16'))

        txtNumber = StringVar()
        txtAmount = StringVar()
        txtTips = StringVar()

        txtTips.set('请输入相关信息')
        LabelTips = Label(Monty, textvariable=txtTips, font=('华文新魏', '16'), relief='ridge', width=30)
        entryNumber = Entry(Monty, textvariable=txtNumber, width=16, font=('宋体', '16'))
        entryAmount = Entry(Monty, textvariable=txtAmount, width=16, font=('宋体', '16'))

        buttonSub = Button(Monty, text='提交', command=updata_books, font=('宋体', '16'))


        LabelNumber.grid(row=0, column=0)
        LabelAmount.grid(row=1, column=0)
        LabelTips.grid(row=2, column=0, columnspan=2)
        entryNumber.grid(row=0, column=1)
        entryAmount.grid(row=1, column=1)
        buttonSub.grid(row=2, column=2)

        child.mainloop()
    # 删除图书信息
    def delbook():
        child = tk.Toplevel()
        child.geometry('450x150')
        child.title('书库存管理系统')
        Monty = ttk.LabelFrame(child, text='删除库存信息')
        Monty.grid(row=0, column=0, padx=10, pady=10)
        conn = sqlite3.connect("test.db")
        cur = conn.cursor()


        def del_books():
            try:
                sql_del='DELETE FROM user WHERE num = %s'
                cur.execute(sql_del %txtNumber.get())
                conn.commit()
                conn.close()
            except Exception:
                txtTips.set('书号不正确')
            else:
                txtTips.set('图书已删除')


        LabelNumber = Label(Monty, text="书号：", font=('华文新魏', '16'))


        txtNumber = StringVar()
        txtTips = StringVar()

        txtTips.set('请输入相关信息')
        LabelTips = Label(Monty, textvariable=txtTips, font=('华文新魏', '16'), relief='ridge', width=30)
        entryNumber = Entry(Monty, textvariable=txtNumber, width=16, font=('宋体', '16'))


        buttonSub = Button(Monty, text='删除', command=del_books, font=('宋体', '16'))


        LabelNumber.grid(row=0, column=0)
        LabelTips.grid(row=1, column=0, columnspan=2)
        entryNumber.grid(row=0, column=1)
        buttonSub.grid(row=1, column=2)


    # 退出
    def Systemexit():
        exit()


    buttonBook = Button(Monty, text='显示书目信息',command=booklist,  font=('宋体', '16'), width=16)
    buttonAdd = Button(Monty, text='添加书目信息',command=addbook, font=('宋体', '16'), width=16)
    buttonUpdata = Button(Monty,text='修改书目信息',command=updatabook,font=('宋体','16'),width=16)
    buttonDel = Button(Monty,text='删除图书信息',command=delbook,font=('宋体','16'),width=16)
    buttonExit = Button(Monty, text='退出', command=Systemexit, font=('宋体', '16'), width=16)
    buttonBook.grid(row=0, column=0)
    buttonAdd.grid(row=1, column=0)
    buttonUpdata.grid(row=2,column=0)
    buttonDel.grid(row=3,column=0)
    buttonExit.grid(row=4, column=0)
    win.mainloop()

